/*

Select SysObjects.[Name] As [Contraint Name] ,Tab.[Name] as [Table Name],Col.[Name] As [Column Name]
 From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab
On Tab.[ID] = Sysobjects.[Parent_Obj] 
Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID] 
Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
order by Tab.[Name]
*/

drop procedure dbo.drop_col  
go

create procedure dbo.drop_col ( @tbl varchar(100), @col varchar(100))
as
begin
	declare @cons_name varchar(200), @a varchar(1000),  @mySql varchar(1000)

	print @tbl + ' ' + @col

	-- check presence of constraint
	Select @cons_name  = SysObjects.[Name]    
		/*	,Tab.[Name] as [Table Name],
			Col.[Name] As [Column Name]
        */
	From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U' and name = @tbl ) As Tab On Tab.[ID] = Sysobjects.[Parent_Obj] 
		Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID] 
		Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
	where col.name = @col

	print @cons_name
	
	if ( @cons_name is not null ) 
	begin
		set @mySql = 'alter table '  + @tbl  + ' drop constraint ' + @cons_name  
	 
		exec ( @mySql )
		print ' drop constraint ' + @cons_name 
	end

	set @mySql = ' if exists ( select a.name, 
                       b.name from  sysobjects a,
                                   syscolumns b
               where b.id = a.id
                 and a.name = ' +  '''' + @tbl + '''' 
                  + ' and b.name = ' + '''' + @col + '''' + 
             ' )
	alter table ' + @tbl + 
		' drop column  ' + @col  

	print @mySql

	exec ( @mySql )

	print ' drop column ' + @col 
	print 'done'
end

/*
	exec dbo.drop_col 'sabrs_feed_201006', 'funding_type'

	if exists ( select a.name, 
                       b.name from  sysobjects a,
                                   syscolumns b
               where b.id = a.id
                 and a.name = 'sabrs_feed_201006'
                 and b.name = 'funding_type'
              )
	alter table sabrs_feed_201006
		drop column  funding_type 
		
	select   * from syscolumns
	where name = 'funding_type'

	select * from sysobjects
where id = 11863109


*/

